﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using DataHelper;

namespace DAL
{
    public class AccountDao
    {
        public bool ExecSQL(string strSQL)
        {
            return SQLHelper.ExecuteSqlWithTransaction(strSQL);
        }
        public bool UpdateStatu(string account,string statu)
        {
            string strSQL = "UPDATE Sys_Account SET State = '" + statu + "' WHERE Account = '" + account + "'";
            return SQLHelper.ExecuteSql(strSQL);
        }
        public bool DeleteOneInfo(string account)
        {
            string strSQL = "delete from Sys_AccountMenu WHERE Account='" + account + "' ;delete from Sys_Account_Role WHERE Account='" + account + "';DELETE FROM Sys_Account WHERE Account = '" + account + "'";
            return SQLHelper.ExecuteSqlWithTransaction(strSQL);
        }
        public DataTable GetOneInfo(string account)
        {
            string strSQL = "SELECT * from JimView_Account WHERE Account ='" + account + "'";
            return SQLHelper.ExecuteSqlGetDataSet(strSQL).Tables[0];
        }
        //login 验证
        public DataTable GetOneInfo(string account, string pwd)
        {
            string strSQL = "SELECT * from JimView_Account WHERE Account ='" + account + "' and  Password = '" + pwd + "' AND State = 'Y'";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        public DataTable GetAllInfo(string empCode)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("SELECT * from JimView_Account WHERE EmpCode ='" + empCode + "'");
        }
        public DataTable GetLists(string statu)
        {
            if (statu == "")
                return SQLHelper.ExecuteSqlGetDrToDt("Select Account as ID,EmpName+'('+Account+')' as Name from JimView_Account Order by [State] DESC");
            else
                return SQLHelper.ExecuteSqlGetDrToDt("Select Account as ID,EmpName+'('+Account+')' as Name from JimView_Account where [State]='" + statu + "' Order by [State] DESC ");
        }
        //账号管理界面的查询
        public DataTable GetLists(string serWord, string deptID, string statu)
        {
            string strSearchSQL = "SELECT  AccountID,EmpCode as 工号, EmpName AS 姓名, Account AS 帐号, DutyName as 职位,dbo.GetSysRoleIDByAccount(EmpCode) AS 角色, State AS 状态, Memo AS 说明 FROM JimView_Account WHERE 1=1 ";
            if (!string.IsNullOrWhiteSpace(serWord))
            {
                strSearchSQL += " and (EmpCode  like  '%" + serWord + "%' OR Account like  '%" + serWord + "%' OR EmpName like  '%" + serWord + "%') ";
            }
            else
            {
                if (statu == "NY")
                    strSearchSQL += " and State in ('N','Y') ";
                else if (statu == "N")
                    strSearchSQL += " and State ='N' ";
                else if (statu == "Y")
                    strSearchSQL += " and State ='Y' ";
                else if (statu == "H")
                    strSearchSQL += " and State ='H' ";
                if (deptID != "0")
                    strSearchSQL += " and DeptID = " + deptID;
            }
            strSearchSQL += "  ORDER BY State DESC";
            //  return SQLHelper.ExecuteSqlGetDrToDt("Select Account as ID,EmpName+'('+Account+')' as Name from JimView_Account Order by [State] DESC");
            return SQLHelper.ExecuteSqlGetDrToDt(strSearchSQL);
        }
        //获取一个部门下 的账号列表  客户管理界面的下拉列表使用
        public DataTable GetListByDeptID(string deptID)
        {
            string strSQL = "select EmpName,Account from JimView_Account where DeptID=" + deptID +" and State ='Y' order by EmpCode";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }
        public bool CopyRightsTo(string sourceAccount, string receiveAccount, bool copyType)
        {
            if (copyType)
                return SQLHelper.ExecuteSql("insert into Sys_AccountMenu (account,MainMenuID,RightLimit)  select  '" + receiveAccount + "',MainMenuID,RightLimit  from Sys_AccountMenu a where a.account='" + sourceAccount + "' and MainMenuID not in (select MainMenuID from Sys_AccountMenu where account='" + receiveAccount + "')");
            else
                return SQLHelper.ExecuteSql("Delete from Sys_AccountMenu Where account='" + receiveAccount + "';insert into Sys_AccountMenu (account,MainMenuID,RightLimit)  select  '" + receiveAccount + "',MainMenuID,RightLimit  from Sys_AccountMenu a where a.account='" + sourceAccount + "'");
        }
        //获取该账号的所有菜单,单个选择的菜单
        public DataTable GetMenuByAccount(string account)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("select * from JimView_Account_Menu Where Account ='" + account + "' and State='Y' order by ParentID asc,AbsIndex DESC");
        }
        //设置right
        public bool SetMenuRight(ArrayList menuArray, string strRight, string rangeLimit)
        {
            bool flag = true;
            if (menuArray.Count > 0)
            {
                StringBuilder stringBuilder = new StringBuilder();
                foreach (object accountMenuID in menuArray)
                {
                    stringBuilder.Append("update Sys_AccountMenu Set RightLimit='" + strRight + "',RangeLimit='" + rangeLimit + "' Where AccountMenuID=" + accountMenuID.ToString() + ";");
                }
                string strSQL = stringBuilder.ToString();
                if (!string.IsNullOrEmpty(strSQL))
                    flag = SQLHelper.ExecuteSql(stringBuilder.ToString());
                else
                    flag = false;
            }
            else
            {
                flag = false;

            }
            return flag;
        }


        //检测权限 RightLimit

        public static bool CheckRight(string account, string menuUrl, RightChar rightChar)
        {
            if (string.IsNullOrWhiteSpace(account)) return false;

            int i = (int)rightChar;
            DataTable dt = SQLHelper.ExecuteSqlGetDataSet("select RightLimit from JimView_Account_Menu Where Account ='" + account + "' And LinkUrl like '%" + menuUrl + "%' and State='Y'").Tables[0];
            if (dt.Rows.Count < 1)
                dt = SQLHelper.ExecuteSqlGetDataSet("select RightLimit from JimView_Role_Menu Where SysRoleID in (select SysRoleID FROM Sys_Account_Role WHERE Account ='" + account + "') And LinkUrl like '%" + menuUrl + "%' and State='Y'").Tables[0];
            if (dt.Rows.Count < 1)
                return false;
            string strRight = dt.Rows[0]["RightLimit"].ToString();
            if (string.IsNullOrWhiteSpace(strRight)) return false;

            if (strRight.Substring(i, 1) == "1")
                return true;
            else
                return false;

        }
        //判断是否在超级用户组
        public static bool IsSuperGroup(string account)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDataSet("select * from Sys_Account_Role where SysRoleID='admin' and Account='" + account + "'").Tables[0];
            if (dt.Rows.Count > 0)//超级权限
                return true;
            else //普通那个权限
                return false;
        }
        //获取本表单， 权限限制范围 RangeLimit
        public static RangeChar GetRange(string account, string menuUrl)
        {
            RangeChar rangeChar = RangeChar.Normal;//普通权限不用处理
            if (string.IsNullOrWhiteSpace(account)) return rangeChar;
            if (AccountDao.IsSuperGroup(account))   return RangeChar.Super;
            //账号菜单
            DataTable  dt = SQLHelper.ExecuteSqlGetDataSet("select RangeLimit from JimView_Account_Menu Where Account ='" + account + "' And LinkUrl like '%" + menuUrl + "%' and State='Y'").Tables[0];
            if (dt.Rows.Count < 1)
                //角色菜单
                dt = SQLHelper.ExecuteSqlGetDataSet("select RangeLimit from JimView_Role_Menu Where SysRoleID in (select SysRoleID from Sys_Account_Role where  Account ='" + account + "') And LinkUrl like '%" + menuUrl + "%' and State='Y'").Tables[0];
          
            if (dt.Rows.Count < 1)
                return rangeChar;
            string strRange = dt.Rows[0]["RangeLimit"].ToString();
            if (string.IsNullOrWhiteSpace(strRange)) return rangeChar;
            return (RangeChar)Int32.Parse(strRange);
      }
       
    }
}
